﻿/**

 * Copyright (c) 2015-2016, FastDev 刘强 (fastdev@163.com) & Quincy.

 *

 * Licensed under the Apache License, Version 2.0 (the "License");

 * you may not use this file except in compliance with the License.

 * You may obtain a copy of the License at

 *

 *      http://www.apache.org/licenses/LICENSE-2.0

 *

 * Unless required by applicable law or agreed to in writing, software

 * distributed under the License is distributed on an "AS IS" BASIS,

 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.

 * See the License for the specific language governing permissions and

 * limitations under the License.

 */

using OF.DB.DataAccess;
using log4net;
using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Reflection.Emit;
using System.Security.Cryptography;
using System.Text;
using System.Web;
using OF.DB.DataAccess.Query;

namespace OF.DB.DataAccess
{
    public static class DBHelper
    {
        private static MethodInfo DataReaderGetItemMethod = typeof(DbDataReader).GetMethod("get_Item", new Type[] { typeof(string) });
        private static MethodInfo DataReaderReadMethod = typeof(DbDataReader).GetMethod("Read", new Type[0]);
        private static FieldInfo DBNullValueField = typeof(System.DBNull).GetField("Value");
        private static Type NullableType = typeof(Nullable<>).GetGenericTypeDefinition();
        private static MethodInfo GetTypeFromHandleMethod = typeof(Type).GetMethod("GetTypeFromHandle", new Type[] { typeof(RuntimeTypeHandle) });
        private static MethodInfo ChangeTypeMethod = typeof(Convert).GetMethod("ChangeType", new Type[] { typeof(object), typeof(Type) });
        private static MethodInfo HashSetContainsMethod = typeof(HashSet<string>).GetMethod("Contains", new Type[] { typeof(string) });
        private static Type DateTimeType = typeof(DateTime);
        private static Type DecimalType = typeof(decimal);
        private static Type StringType = typeof(string);
        private static Type GuidType = typeof(Guid);
        private static Type dbType = typeof(DbType);
        private static Type ByteArrayType = typeof(byte[]);

        private static ConcurrentDictionary<Type, Delegate> delegateDict = new ConcurrentDictionary<Type, Delegate>();
        private static string ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["DBConn"].ConnectionString;
        public const int PageSize = 15;

        public static bool EnableTraceSql = false;
        public static Func<DateTime> DateTimeNowFunc = () => { return DateTime.Now; };

        public static string DefaultEntityNameSpace = "OF.DB.Entity";

        public static Func<SqlDataReader, List<T>> GetEntityConverter<T>() where T : class
        {
            Type type = typeof(T);
            Delegate converter = null;
            if (delegateDict.TryGetValue(type, out converter))
            {
                return (Func<SqlDataReader, List<T>>)converter;
            }
            else
            {
                Func<SqlDataReader, List<T>> result = GetListSetter<T>();
                delegateDict.TryAdd(type, result);
                return result;
            }
        }

        public static int ExecuteNonQuery(string sql, SqlParameter[] sqlParams, int? timeOut = null)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(ConnectionString))
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand(sql, conn))
                    {
                        if (timeOut != null)
                        {
                            cmd.CommandTimeout = timeOut.Value;
                        }
                        if (sqlParams != null)
                        {
                            EnsureParameterValues(sqlParams);
                            cmd.Parameters.AddRange(sqlParams);
                        }
                        return cmd.ExecuteNonQuery();
                    }
                }
            }
            catch
            {
                throw;
            }
        }

        public static object ExecuteScale(string sql, SqlParameter[] sqlParams, int? timeOut = null)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(ConnectionString))
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand(sql, conn))
                    {
                        if (timeOut != null)
                        {
                            cmd.CommandTimeout = timeOut.Value;
                        }
                        if (sqlParams != null)
                        {
                            EnsureParameterValues(sqlParams);
                            cmd.Parameters.AddRange(sqlParams);
                        }
                        return cmd.ExecuteScalar();
                    }
                }
            }
            catch
            {
                throw;
            }
        }

        public static object GetParamValue(object value)
        {
            if (value == null)
            {
                return DBNull.Value;
            }
            else
            {
                return value;
            }
        }

        public static T GetFirstOrDefault<T>(List<T> list)
        {
            if (list == null || list.Count == 0)
            {
                return default(T);
            }
            else
            {
                return list[0];
            }
        }

        private static string GetParamSql(SqlParameter[] sqlParams)
        {
            if (sqlParams == null)
            {
                return string.Empty;
            }
            string parmArr = string.Join(",", sqlParams.Select(item => string.Format("new SqlParameter(\"@{0}\", {1}) {{ Value = {2}}}", item.ParameterName, item.SqlDbType, item.SqlValue)));
            string result = string.Format("new SqlParameter[]{{ {0} }}", parmArr);
            return result;
        }

        public static List<T> ExecuteReader<T>(string sql, SqlParameter[] sqlParams, int? timeOut = null) where T : class
        {
            try
            {
                if (EnableTraceSql)
                {
                    Util.DumpFile(DBHelper.GetSqlDBDefine(sql, sqlParams));
                }
            }
            catch (Exception ex)
            {
                Util.DumpFile(ex.ToString());
            }
            /*
            string paramSql = GetParamSql(sqlParams);
            string dumpSql = "ExecuteReader:" + sql + "\n" + paramSql;
            //TestPerformance.Begin(dumpSql);
            */
            try
            {
                var func = GetEntityConverter<T>();
                using (SqlConnection conn = new SqlConnection(ConnectionString))
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand(sql, conn))
                    {
                        if (timeOut != null)
                        {
                            cmd.CommandTimeout = timeOut.Value;
                        }
                        if (sqlParams != null)
                        {
                            EnsureParameterValues(sqlParams);
                            cmd.Parameters.AddRange(sqlParams);
                        }
                        using (SqlDataReader reader = cmd.ExecuteReader())
                        {
                            /*
                            //TestPerformance.End(dumpSql);
                             */
                            return func(reader);
                        }
                    }
                }
            }
            catch
            {
                throw;
            }
        }

        private static void EnsureParameterValues(SqlParameter[] sqlParams)
        {
            foreach (var param in sqlParams)
            {
                if (param.Value == null)
                {
                    param.Value = DBNull.Value;
                }
            }
        }

        public static HashSet<string> GetColumnSet(SqlDataReader reader)
        {
            HashSet<string> result = new HashSet<string>();
            for (int i1 = 0; i1 < reader.FieldCount; i1++)
            {
                result.Add(reader.GetName(i1));
            }
            return result;
        }

        private enum PrecisionType
        {
            None,
            HasPrecision,
            HasPrecisionScale
        }

        public static string GetSafeName(string tableName)
        { 
            if(tableName.StartsWith("[") && tableName.EndsWith("]"))  
            {
                return tableName;
            }
            else
            {
                return string.Format("[{0}]", tableName);
            }
        }

        public static string GetDonetEntityDefine(string tableName)
        {
            StringBuilder sb = new StringBuilder();
            string result = null;
            string sql = string.Format("dbo.SP_COLUMNS {0}", GetSafeName(tableName));//SELECT TOP 1 * FROM dbo.{0} WITH(NOLOCK) WHERE 1 = 0
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            sb.AppendLine(GetDonetFieldBySqlType(reader));
                        }
 
                    }
                }
            }
            string entityFormat = @"
using System;
using System.Collections.Generic;
namespace {2}
{{
    public class {0}
    {{ 
{1}
    }}
}}";  
            result = string.Format(entityFormat, tableName, sb.ToString(), DefaultEntityNameSpace);
            string path = DBGenerator.InitOutputPath(string.Format("{0}.cs", tableName));
            DBGenerator.Output(path, result);
            return result;
        }

        public static Tuple<string, bool> GetDonetFieldBySqlType(string dbTypeName)
        {
            string donetType = null;
            bool isTypeNullable = false;
            switch (dbTypeName)
            {
                case "int":
                case "int identity":
                    donetType = "int";
                    break;
                case "bit":
                    donetType = "bool";
                    break;
                case "datetime":
                    donetType = "DateTime";
                    break;
                case "image":
                    donetType = "byte[]";
                    isTypeNullable = true;
                    break;
                case "money":
                    donetType = "decimal";
                    break;
                case "smalldatetime":
                    donetType = "DateTime";
                    break;
                case "smallint":
                    donetType = "short";
                    break;
                case "bigint":
                    donetType = "long";
                    break;
                case "text":
                    donetType = "string";
                    isTypeNullable = true;
                    break;
                case "numeric":
                    donetType = "decimal";
                    break;
                case "real":
                    donetType = "decimal";
                    break;
                case "smallmoney":
                    donetType = "decimal";
                    break;
                case "sql_variant":
                    donetType = "object";
                    isTypeNullable = true;
                    break;
                case "timestamp":
                    donetType = "DateTime";
                    break;
                case "tinyint":
                    donetType = "byte";
                    break;
                case "uniqueidentifier":
                    donetType = "Guid";
                    break;
                case "xml":
                    donetType = "string";
                    isTypeNullable = true;
                    break;
                case "varchar":
                    donetType = "string";
                    isTypeNullable = true;
                    break;
                case "float":
                    donetType = "float";
                    break;
                case "ntext":
                    donetType = "string";
                    isTypeNullable = true;
                    break;
                case "nvarchar":
                    donetType = "string";
                    isTypeNullable = true;
                    break;
                case "binary":
                    donetType = "byte[]";
                    isTypeNullable = true;
                    break;
                case "char":
                    donetType = "string";
                    isTypeNullable = true;
                    break;
                case "nchar":
                    donetType = "string";
                    isTypeNullable = true;
                    break;
                case "varbinary":
                    donetType = "byte[]";
                    isTypeNullable = true;
                    break;
                //size, scale
                case "decimal":
                    donetType = "decimal";
                    break;
                default:
                    donetType = "..." + dbTypeName;
                    break;
            }
            return new Tuple<string, bool>(donetType, isTypeNullable);
        }

        private static string GetDonetFieldBySqlType(SqlDataReader readr)
        {
            string columnName = readr["COLUMN_NAME"].ToString();
            string dbTypeName = readr["TYPE_NAME"].ToString();
            bool isNullable = readr["NULLABLE"].ToString() == "1";
            bool isTypeNullable = false;
            string donetType = null;
            Tuple<string, bool> typeTuple = GetDonetFieldBySqlType(dbTypeName);
            donetType = typeTuple.Item1;
            isTypeNullable = typeTuple.Item2;
            string flag = string.Empty;
            if (isNullable && !isTypeNullable)
            {
                flag = "?";
            }
            return string.Format("public {0}{1} {2};", donetType, flag, columnName);
        }

        private static string GetSqlParamDefine(SqlDataReader readr, out bool isIdentity)
        {
            isIdentity = false;
            string columnName = readr["COLUMN_NAME"].ToString();
            string typeName = readr["TYPE_NAME"].ToString();
            string precision = readr["PRECISION"].ToString();
            string scale = readr["SCALE"].ToString();
            SqlDbType dbType = SqlDbType.Variant;
            PrecisionType precisionType = PrecisionType.None;
            switch (typeName)
            {
                case "int":
                    dbType = SqlDbType.Int;
                    break;
                case "int identity":
                    dbType = SqlDbType.Int;
                    isIdentity = true;
                    break;
                case "bit":
                    dbType = SqlDbType.Bit;
                    break;
                case "datetime":
                    dbType = SqlDbType.DateTime;
                    break;
                case "image":
                    dbType = SqlDbType.Image;
                    break;
                case "money":
                    dbType = SqlDbType.Money;
                    break;
                case "smalldatetime":
                    dbType = SqlDbType.SmallDateTime;
                    break;
                case "smallint":
                    dbType = SqlDbType.SmallInt;
                    break;
                case "bigint":
                    dbType = SqlDbType.BigInt;
                    break;
                case "text":
                    dbType = SqlDbType.Text;
                    break;
                case "numeric":
                    dbType = SqlDbType.Decimal;
                    break;
                case "real":
                    dbType = SqlDbType.Real;
                    break;
                case "smallmoney":
                    dbType = SqlDbType.SmallMoney;
                    break;
                case "sql_variant":
                    dbType = SqlDbType.Variant;
                    break;
                case "timestamp":
                    dbType = SqlDbType.Timestamp;
                    break;
                case "tinyint":
                    dbType = SqlDbType.TinyInt;
                    break;
                case "uniqueidentifier":
                    dbType = SqlDbType.UniqueIdentifier;
                    break;
                case "xml":
                    dbType = SqlDbType.Xml;
                    break;


                //size, no scale
                case "varchar":
                    dbType = SqlDbType.VarChar;
                    precisionType = PrecisionType.HasPrecision;
                    break;
                case "float":
                    dbType = SqlDbType.Float;
                    precisionType = PrecisionType.HasPrecision;
                    break;
                case "ntext":
                    dbType = SqlDbType.NText;
                    precisionType = PrecisionType.HasPrecision;
                    break;
                case "nvarchar":
                    dbType = SqlDbType.NVarChar;
                    precisionType = PrecisionType.HasPrecision;
                    break;
                case "binary":
                    dbType = SqlDbType.Binary;
                    precisionType = PrecisionType.HasPrecision;
                    break;
                case "char":
                    dbType = SqlDbType.Char;
                    precisionType = PrecisionType.HasPrecision;
                    break;
                case "nchar":
                    dbType = SqlDbType.NChar;
                    precisionType = PrecisionType.HasPrecision;
                    break;
                case "varbinary":
                    dbType = SqlDbType.VarBinary;
                    precisionType = PrecisionType.HasPrecision;
                    break;


                //size, scale
                case "decimal":
                    dbType = SqlDbType.Decimal;
                    precisionType = PrecisionType.HasPrecisionScale;
                    break;
            }
            string dbTypeName = Enum.GetName(typeof(SqlDbType), dbType);
            if (precisionType.Equals(PrecisionType.None))
            {
                return string.Format("new SqlParameter(\"@{0}\", SqlDbType.{1}) {{ Value = DBHelper.GetParamValue(entity.{0}) }}", columnName, dbTypeName);
            }
            else if (precisionType.Equals(PrecisionType.HasPrecision))
            {
                return string.Format("new SqlParameter(\"@{0}\", SqlDbType.{1}) {{ Value = DBHelper.GetParamValue(entity.{0}) }}", columnName, dbTypeName, precision); //Precision = {2}, 
            }
            else//(precisionType.Equals(PrecisionType.HasPrecisionScale))
            {
                return string.Format("new SqlParameter(\"@{0}\", SqlDbType.{1}) {{ Precision = {2}, Scale = {3}, Value = DBHelper.GetParamValue(entity.{0}) }}", columnName, dbTypeName, precision, scale);
            }
        }



        public static bool IsExists(Tuple<string, List<SqlParameter>> sqlDefineTuple)
        {
            string sql = string.Format(sqlDefineTuple.Item1, string.Empty);
            string existSql = string.Format("SELECT TOP 1 Result = CAST(1 AS BIT) FROM ({0}) AS Tbl", sql);
            List<GenericResultEntity<bool>> isExitsList = DBHelper.ExecuteReader<GenericResultEntity<bool>>(existSql, sqlDefineTuple.Item2.ToArray());
            if (isExitsList == null || isExitsList.Count == 0)
            {
                return false;
            }
            else
            {
                return isExitsList[0].Result;
            }
        }

        public static T GetFirstOrDefault<T>(string sql, SqlParameter[] sqlParams) where T : class
        {
            string firstSql = string.Format("SELECT TOP 1 * FROM ({0}) AS Tbl", sql);
            List<T> list = DBHelper.ExecuteReader<T>(firstSql, sqlParams);
            return DBHelper.GetFirstOrDefault(list);
        }

        public static string GetInCondition(List<int> idList)
        {
            if (idList == null || idList.Count == 0)
            {
                return "NULL";
            }
            else
            {
                return string.Join(",", idList);
            }
        }

        public static Nullable<T> ConvertScaleResult<T>(object scaleResult) where T : struct
        {
            if (scaleResult == null || scaleResult.Equals(DBNull.Value))
            {
                return null;
            }
            else
            {
                return (T)Convert.ChangeType(scaleResult, typeof(T));
            }
        }

        public static string ConvertDbToString(object scaleResult)
        {
            if (scaleResult == null || scaleResult.Equals(DBNull.Value))
            {
                return null;
            }
            else
            {
                return scaleResult.ToString();
            }
        }
        //public delegate List<T> MyFuncList<T>(SqlDataReader reader);
        public static Func<SqlDataReader, List<T>> GetListSetter<T>() where T : class
        {
            var listType = typeof(List<T>);
            DynamicMethod dm = new DynamicMethod("getEntityList", listType, new Type[] { typeof(SqlDataReader) }, typeof(object), true);
            ILGenerator ilg = dm.GetILGenerator();

            LocalBuilder e = ilg.DeclareLocal(typeof(T));//声明指定类型的局部变量 可以T t;这么理解
            LocalBuilder obj = ilg.DeclareLocal(typeof(object));
            LocalBuilder result = ilg.DeclareLocal(listType);
            LocalBuilder columnMap = ilg.DeclareLocal(typeof(HashSet<string>));
            ilg.Emit(OpCodes.Ldnull);
            ilg.Emit(OpCodes.Stloc, e);
            ilg.Emit(OpCodes.Ldnull);
            ilg.Emit(OpCodes.Stloc, obj);
            ilg.Emit(OpCodes.Newobj, listType.GetConstructor(Type.EmptyTypes));
            ilg.Emit(OpCodes.Stloc, result);
            ilg.Emit(OpCodes.Ldarg_0);
            ilg.Emit(OpCodes.Call, typeof(DBHelper).GetMethod("GetColumnSet", new Type[] { typeof(SqlDataReader) }));
            ilg.Emit(OpCodes.Stloc, columnMap);
            Label loopCeil = ilg.DefineLabel();//IL_00ec
            ilg.Emit(OpCodes.Br, loopCeil);

            Label loopRow = ilg.DefineLabel();
            ilg.MarkLabel(loopRow);
            ilg.Emit(OpCodes.Newobj, typeof(T).GetConstructor(new Type[0]));
            ilg.Emit(OpCodes.Stloc, e);

            foreach (var field in typeof(T).GetFields(BindingFlags.Public | BindingFlags.CreateInstance | BindingFlags.Instance))
            {   
                var fieldType = field.FieldType;
                if (fieldType.IsPrimitive
                    || (fieldType.IsGenericType && fieldType.GetGenericTypeDefinition().Equals(NullableType))
                    || fieldType.Equals(StringType)
                    || fieldType.Equals(DateTimeType)
                    || fieldType.Equals(DecimalType)
                    || fieldType.IsEnum
                    || fieldType.Equals(GuidType)
                    || fieldType.Equals(ByteArrayType))
                {
                }
                else
                {
                    continue;
                }
                ilg.Emit(OpCodes.Ldloc, columnMap);
                ilg.Emit(OpCodes.Ldstr, field.Name);
                ilg.Emit(OpCodes.Callvirt, HashSetContainsMethod);
                Label nextItem = ilg.DefineLabel(); //IL_0058
                ilg.Emit(OpCodes.Brfalse_S, nextItem);

                ilg.Emit(OpCodes.Ldarg_0);
                ilg.Emit(OpCodes.Ldstr, field.Name);
                ilg.Emit(OpCodes.Callvirt, DataReaderGetItemMethod);
                ilg.Emit(OpCodes.Dup);
                ilg.Emit(OpCodes.Stloc, obj);
                ilg.Emit(OpCodes.Ldsfld, DBNullValueField);
                ilg.Emit(OpCodes.Beq_S, nextItem);
                ilg.Emit(OpCodes.Ldloc, e);
                ilg.Emit(OpCodes.Ldloc, obj);
                if (fieldType.IsPrimitive || fieldType.Equals(DateTimeType) || fieldType.Equals(DecimalType) || fieldType.IsEnum || fieldType.Equals(GuidType) || fieldType.Equals(ByteArrayType))
                {
                    if (!fieldType.IsEnum && !fieldType.Equals(GuidType) && !fieldType.Equals(ByteArrayType))
                    {
                        ilg.Emit(OpCodes.Ldtoken, fieldType);
                        ilg.Emit(OpCodes.Call, GetTypeFromHandleMethod);
                        ilg.Emit(OpCodes.Call, ChangeTypeMethod);
                    }
                    ilg.Emit(OpCodes.Unbox_Any, fieldType);
                }
                else if (fieldType.IsGenericType && fieldType.GetGenericTypeDefinition().Equals(NullableType))
                {
                    var primitiveType = fieldType.GetGenericArguments()[0];
                    if (!primitiveType.IsEnum && !primitiveType.Equals(GuidType) && !primitiveType.Equals(ByteArrayType))
                    {
                        ilg.Emit(OpCodes.Ldtoken, primitiveType);
                        ilg.Emit(OpCodes.Call, GetTypeFromHandleMethod);
                        ilg.Emit(OpCodes.Call, ChangeTypeMethod);
                    }
                    ilg.Emit(OpCodes.Unbox_Any, primitiveType);
                    ilg.Emit(OpCodes.Newobj, NullableType.MakeGenericType(new Type[] { primitiveType }).GetConstructor(new Type[] { primitiveType }));
                }
                else if (fieldType.Equals(StringType))
                {
                    ilg.Emit(OpCodes.Callvirt, typeof(object).GetMethod("ToString"));
                }
                else
                {
                    throw new NotSupportedException(fieldType.ToString());
                }
                ilg.Emit(OpCodes.Stfld, field);//("set_" + fieldName, new Type[] { fieldType }));
                //typeof(T).GetField(field.Name, BindingFlags.Public | BindingFlags.CreateInstance | BindingFlags.Instance)
                ilg.MarkLabel(nextItem);
            }
            ilg.Emit(OpCodes.Ldloc, result);
            ilg.Emit(OpCodes.Ldloc, e);
            ilg.Emit(OpCodes.Callvirt, listType.GetMethod("Add", new Type[] { typeof(T) }));
            ilg.MarkLabel(loopCeil);
            ilg.Emit(OpCodes.Ldarg_0);
            ilg.Emit(OpCodes.Callvirt, DataReaderReadMethod);
            ilg.Emit(OpCodes.Brtrue, loopRow);
            ilg.Emit(OpCodes.Ldloc, result);
            ilg.Emit(OpCodes.Ret);
            Func<SqlDataReader, List<T>> func = (Func<SqlDataReader, List<T>>)dm.CreateDelegate(typeof(Func<SqlDataReader, List<T>>));
            return func;
        }

        private static string GetParamDBSql(SqlParameter param)
        {
            string paramPricisonScale = string.Empty;
            string value = null;
            if (param.SqlDbType.Equals(SqlDbType.Char)
                || param.SqlDbType.Equals(SqlDbType.NChar)
                || param.SqlDbType.Equals(SqlDbType.VarChar)
                || param.SqlDbType.Equals(SqlDbType.NVarChar)
                || param.SqlDbType.Equals(SqlDbType.Binary)
                || param.SqlDbType.Equals(SqlDbType.VarBinary))
            {
                paramPricisonScale = string.Format("({0})", param.Precision > 0 ? param.Precision : 1000);
                if (param.Value == null || param.Value.Equals(DBNull.Value))
                {
                    value = "NULL";
                }
                else
                {
                    value = string.Format("'{0}'", param.Value);
                }
            }
            else if (param.SqlDbType.Equals(SqlDbType.DateTime))
            {
                paramPricisonScale = string.Empty;
                if (param.Value == null || param.Value.Equals(DBNull.Value))
                {
                    value = "NULL";
                }
                else
                {
                    value = string.Format("'{0}'", ((DateTime)param.Value).ToString("yyyy-MM-dd HH:mm:ss.fff"));
                }
            }
            else
            {
                if (param.SqlDbType.Equals(SqlDbType.Decimal))
                {
                    paramPricisonScale = string.Format("({0}, {1})", param.Precision > 0 ? param.Precision : 14, param.Scale > 0 ? param.Scale : 2);
                }
                else
                {
                    paramPricisonScale = string.Empty;
                }

                if (param.Value == null || param.Value.Equals(DBNull.Value))
                {
                    value = "NULL";
                }
                else
                {
                    value = param.Value.ToString();
                }
            }
            return string.Format("DECLARE {0} {1}{2} = {3};", param.ParameterName, GetDBTypeName(param.SqlDbType), paramPricisonScale, value);
        }

        public static string GetSqlDBDefine(string sql, SqlParameter[] sqlParams)
        {
            StringBuilder sqlBuilder = new StringBuilder(Environment.NewLine);
            foreach (var param in sqlParams)
            {
                sqlBuilder.AppendLine(GetParamDBSql(param));
            }
            sqlBuilder.AppendLine(Environment.NewLine);
            sqlBuilder.AppendLine(sql);
            return sqlBuilder.ToString();
        }

        public static string GetDBTypeName(SqlDbType dbType)
        {
            switch (dbType)
            {
                case SqlDbType.Int:
                    return "int";
                case SqlDbType.Bit:
                    return "bit";
                case SqlDbType.DateTime:
                    return "datetime";
                case SqlDbType.Image:
                    return "image";
                case SqlDbType.Money :
                    return "money";
                case SqlDbType.SmallDateTime:
                    return "smalldatetime";
                case SqlDbType.SmallInt:
                    return "smallint";
                case SqlDbType.BigInt:
                    return "bigint";
                case SqlDbType.Text:
                    return "text";
                case SqlDbType.Real:
                    return "real";
                case SqlDbType.SmallMoney:
                    return "smallmoney";
                case SqlDbType.Variant:
                    return "sql_variant";
                case SqlDbType.Timestamp:
                    return "timestamp";
                case SqlDbType.TinyInt:
                    return "tinyint";
                case SqlDbType.UniqueIdentifier:
                    return "uniqueidentifier";
                case SqlDbType.Xml:
                    return "xml";
                case SqlDbType.VarChar:
                    return "varchar";
                case SqlDbType.Float:
                    return "float";
                case SqlDbType.NText:
                    return "ntext";
                case SqlDbType.NVarChar:
                    return "nvarchar";
                case SqlDbType.Binary:
                    return "binary";
                case SqlDbType.Char:
                    return "char";
                case SqlDbType.NChar:
                    return "nchar";
                case SqlDbType.VarBinary:
                    return "varbinary";
                case SqlDbType.Decimal:
                    return "decimal";
                default:
                    return Enum.GetName(DBHelper.dbType, dbType);
            }
        }

        public static string GetPKColumn(string tableName)
        {
            string sql = string.Format("EXEC sp_pkeys @table_name='{0}'", tableName);//SELECT TOP 1 * FROM dbo.{0} WITH(NOLOCK) WHERE 1 = 0
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            return reader["COLUMN_NAME"].ToString();
                        }
                        else
                        {
                            return null;
                        }
                    }
                }
            }
        }

        public static List<DBColumnMeta> GetTableMeta(string tableName)
        {
            List<DBColumnMeta> result = new List<DBColumnMeta>();
            string sql = string.Format("dbo.SP_COLUMNS {0}", GetSafeName(tableName));//SELECT TOP 1 * FROM dbo.{0} WITH(NOLOCK) WHERE 1 = 0

            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            DBColumnMeta meta = new DBColumnMeta();
                            string columnName = reader["COLUMN_NAME"].ToString();
                            meta.ColumnName = columnName;
                            bool isIdentity = false;
                            meta.DBTypeName = reader["TYPE_NAME"].ToString();
                            meta.SqlParamDefine = GetSqlParamDefine(reader, out isIdentity);
                            meta.IsIdentity = isIdentity;
                            result.Add(meta);
                        }
                        return result;
                    }
                }
            }
        }

        public static List<T> ExecuteOrderedTopN<T>(CommonSortParam sortParam) where T : class
        {
            string orderSqlFormat = @"SELECT TOP {0} * FROM ({1}) AS TBL {2} ORDER BY {3}";
            string sql = null;
            string sqlConditon = null;
            List<SqlParameter> paramList = new List<SqlParameter>();
            if (sortParam.IsFirst)
            {
                sqlConditon = string.Empty;
            }
            else
            {
                StringBuilder sbCondition = new StringBuilder();
                for (int keyI = 0; keyI < sortParam.ColumnSortValueList.Count; keyI++)
                {
                    if (keyI == 0)
                    {
                        sbCondition.Append(" WHERE(");
                    }
                    else
                    {
                        sbCondition.Append(" OR(");
                    }
                    for (int visitI = 0; visitI <= keyI; visitI++)
                    {
                        ColumnSortValue columnSortValue = sortParam.ColumnSortValueList[visitI];
                        if (visitI > 0)
                        {
                            sbCondition.Append(" AND ");
                        }
                        if (visitI < keyI)
                        {
                            sbCondition.Append(string.Format("{0} = @{0}", columnSortValue.ColumnName));
                        }
                        else
                        {
                            sbCondition.Append(string.Format("{0} {1} @{0}", columnSortValue.ColumnName, columnSortValue.IsAsc ? ">" : "<"));
                        }
                    }
                    sbCondition.Append(")");
                    ColumnSortValue columnSortValue2 = sortParam.ColumnSortValueList[keyI];
                    paramList.Add(new SqlParameter("@" + columnSortValue2.ColumnName, columnSortValue2.ColumnType) { Value = columnSortValue2.ColumnValue });
                }
                sqlConditon = sbCondition.ToString();
            }
            sql = string.Format(orderSqlFormat, sortParam.Top, sortParam.Query, sqlConditon, string.Join(",", sortParam.ColumnSortValueList.Select(item => item.ColumnName + " " + (item.IsAsc ? "ASC" : "DESC"))));
            return ExecuteReader<T>(sql, paramList.ToArray());
        }

        public static List<T> GetLatestItems<T>(string sql, string column) where T : class
        {
            return DBHelper.ExecuteOrderedTopN<T>(new CommonSortParam
            {
                ColumnSortValueList = new List<ColumnSortValue> {
                    new ColumnSortValue {
                        ColumnName = column,
                        ColumnType = SqlDbType.DateTime,
                        ColumnValue = null,
                        IsAsc = false
                    },
                    new ColumnSortValue {
                        ColumnName = "id",
                        ColumnType = SqlDbType.Int,
                        ColumnValue = null,
                        IsAsc = true
                    }	
                },
                IsFirst = true,
                Query = sql,
                Top = DBHelper.PageSize
            });
        }

        public static List<T> GetNextItems<T>(string sql, string column, DateTime minDate, int maxId) where T : class
        {
            return DBHelper.ExecuteOrderedTopN<T>(new CommonSortParam
            {
                ColumnSortValueList = new List<ColumnSortValue> {
                    new ColumnSortValue {
                        ColumnName = column,
                        ColumnType = SqlDbType.DateTime,
                        ColumnValue = minDate,
                        IsAsc = false
                    },
                    new ColumnSortValue {
                        ColumnName = "id",
                        ColumnType = SqlDbType.Int,
                        ColumnValue = maxId,
                        IsAsc = true
                    }	
                },
                IsFirst = false,
                Query = sql,
                Top = DBHelper.PageSize
            });
        }

        public static List<T> GetPreviousItems<T>(string sql, string column, DateTime maxPubDate, int minId) where T : class
        {
            return DBHelper.ExecuteOrderedTopN<T>(new CommonSortParam
            {
                ColumnSortValueList = new List<ColumnSortValue> {
                    new ColumnSortValue {
                        ColumnName = column,
                        ColumnType = SqlDbType.DateTime,
                        ColumnValue = maxPubDate,
                        IsAsc = true
                    },
                    new ColumnSortValue {
                        ColumnName = "id",
                        ColumnType = SqlDbType.Int,
                        ColumnValue = minId,
                        IsAsc = false
                    }	
                },
                IsFirst = false,
                Query = sql,
                Top = DBHelper.PageSize
            });
        }

        public static string GetPageSql(int pageIndex, int pageSize, string query, string orderByFields)
        {
            string sql = string.Format(@"SELECT * FROM(SELECT TOP {2} *, 
            ROWNUM = ROW_NUMBER() OVER(ORDER BY {1}) FROM ({0}) AS TBL ORDER BY {1}) 
                AS TBL2 WHERE ROWNUM > {3} ORDER BY ROWNUM", query, orderByFields, pageIndex * pageSize, (pageIndex - 1) * pageSize);
            sql = sql.Replace(Environment.NewLine, " ").Replace("\t", " ");
            return sql;
        }

        public static string GetCountSql(string query)
        {
            string sql = string.Format(@"SELECT COUNT(*) FROM ({0}) AS TBLCOUNT", query);
            sql = sql.Replace(Environment.NewLine, " ").Replace("\t", " ");
            return sql;
        }

        public static int Count(SqlQueryContext context)
        {
            int total = (int)DBHelper.ExecuteScale(context.DeclareVariables + DBHelper.GetCountSql(context.Sql), DBHelper.CloneSqlParams(context.Params), null);
            return total;
        }

        public static List<T> GetList<T>(SqlQueryContext context, string orderBy, int pageIndex, int pageSize) where T : class
        {
            string sql = context.DeclareVariables + DBHelper.GetPageSql(pageIndex, pageSize, context.Sql, orderBy);
            List<T> list = DBHelper.ExecuteReader<T>(sql, DBHelper.CloneSqlParams(context.Params), null);
            return list;
        }

        public static SqlParameter[] CloneSqlParams(SqlParameter[] inParams)
        {
            if (inParams == null)
            { return null; }
            SqlParameter[] result = new SqlParameter[inParams.Length];
            for (int i1 = 0; i1 < inParams.Length; i1++)
            {
                SqlParameter param = inParams[i1];
                result[i1] = new SqlParameter(param.ParameterName, param.SqlDbType) { Value = param.Value };
            }
            return result;
        }
    }

    public class DBColumnMeta
    {
        public string ColumnName;
        public string DBTypeName;
        public string SqlParamDefine;
        public bool IsIdentity;
    }

    public class GenericResultEntity<T>
    {
        public T Result;
    }

    public class CommonSortParam
    {
        public bool IsFirst;
        public int Top;
        public string Query;
        public List<ColumnSortValue> ColumnSortValueList;
    }

    public class ColumnSortValue
    {
        public string ColumnName;
        public SqlDbType ColumnType;
        public object ColumnValue;
        public bool IsAsc;
    }
}

/*
         public static List<T> GetAll<T>(string tableName) where T : class
        {
            string sql = string.Format("SELECT * FROM {0} WITH(NOLOCK)", GetSafeName(tableName));
            return DBHelper.ExecuteReader<T>(sql, null);
        }

        public static List<T> GetIn<T>(string tableName, string idColumn, List<List<int>> idListArray) where T : class
        {
            List<T> result = new List<T>();
            foreach(var list in idListArray)
            {
                string sql = string.Format("SELECT * FROM {0} WITH(NOLOCK) WHERE {1} IN({2})", GetSafeName(tableName), idColumn, GetInCondition(list));
                List<T> tempResult = DBHelper.ExecuteReader<T>(sql, null);
                if (tempResult != null && tempResult.Count > 0)
                {
                    result.AddRange(tempResult);
                }
            }
            return result;
        }
 */